﻿Imports System.Text
Imports MySql.Data.MySqlClient

Public Class DARankeado
    Public Function ListarRankeados(ByVal strIdRanking As String) As List(Of BE.BERankeado)
        Dim objBD As New BancoDeDados
        Dim objDS As DataSet
        Dim lstBERankeados As New List(Of BE.BERankeado)
        Dim sb As New StringBuilder

        sb.Append(" SELECT   v.cpf, Coalesce(u.nome, v.nome) as Nome, Coalesce(f.nome, v.filial) as Filial, u.usuario")
        sb.Append(" FROM ext_vendedor v")
        sb.Append(" INNER JOIN ext_ranking r ON r.id_ranking = v.id_ranking")
        sb.Append(" LEFT JOIN ext_usuario u ON u.usuario = v.usuario")
    sb.Append(" LEFT JOIN ext_franquia f ON f.id_franquia = u.id_franquia")
        sb.Append(" WHERE r.id_ranking = '" & strIdRanking & "'")

        objDS = objBD.ExecutaQuery(sb.ToString())

        For Each objDR As DataRow In objDS.Tables(0).Rows
            Dim objBERankeado As New BE.BERankeado()
            objBERankeado.CPF = objDR("cpf").ToString()
            objBERankeado.IdRanking = strIdRanking
            If objDR("usuario").ToString() <> "" Then
                objBERankeado.Usuario = New BE.BEUsuario(objDR("usuario").ToString(), "")
            Else
                objBERankeado.Usuario = New BE.BEUsuario(objDS.Tables(0).Rows(0)("nome").ToString())
            End If
      objBERankeado.Franquia = objDR("filial").ToString()
            lstBERankeados.Add(objBERankeado)
        Next
        Return lstBERankeados
    End Function

    Public Function SelecionarRankeado(ByVal strIdRanking As String, ByVal strCPF As String) As BE.BERankeado
        Dim objBD As New BancoDeDados
        Dim objDS As DataSet
        Dim objBERankeado As New BE.BERankeado
        Dim sb As New StringBuilder
    Dim objDAUsuario As New DAUsuario()

        sb.Append(" SELECT   v.cpf, Coalesce(u.nome, v.nome) as Nome, Coalesce(f.nome, v.filial) as Filial, u.usuario")
        sb.Append(" FROM ext_vendedor v")
        sb.Append(" INNER JOIN ext_ranking r ON r.id_ranking = v.id_ranking")
        sb.Append(" LEFT JOIN ext_usuario u ON u.usuario = v.usuario")
    sb.Append(" LEFT JOIN ext_franquia f ON f.id_franquia = u.id_franquia")
    sb.Append(" WHERE r.id_ranking = '" & strIdRanking & "' AND v.cpf = '" & strCPF & "'")

        objDS = objBD.ExecutaQuery(sb.ToString())

        If objDS.Tables(0).Rows.Count <> 0 Then
            objBERankeado = New BE.BERankeado()
            objBERankeado.IdRanking = strIdRanking
            objBERankeado.CPF = objDS.Tables(0).Rows(0)("CPF").ToString()
            If objDS.Tables(0).Rows(0)("usuario").ToString() <> "" Then
        Dim objBEUsuario As New BE.BEUsuario()
                objBEUsuario.Usuario = objDS.Tables(0).Rows(0)("usuario").ToString()
                objBERankeado.Usuario = objDAUsuario.SelecionaUsuario(objBEUsuario)
            Else
                objBERankeado.Usuario = New BE.BEUsuario(objDS.Tables(0).Rows(0)("nome").ToString())
            End If
      objBERankeado.Franquia = objDS.Tables(0).Rows(0)("filial").ToString()
        End If

        Return objBERankeado
    End Function

    Public Function BuscarRankeado(ByVal strIdRanking As String, ByVal strCPF As String) As List(Of BE.BERankeado)
        Dim objBD As New BancoDeDados
        Dim objDS As DataSet
        Dim lstBERankeado As New List(Of BE.BERankeado)
        Dim sb As New StringBuilder

        sb.Append(" SELECT   v.cpf, Coalesce(u.nome, v.nome) as Nome, Coalesce(f.nome, v.filial) as Filial, u.usuario")
        sb.Append(" FROM ext_vendedor v")
        sb.Append(" INNER JOIN ext_ranking r ON r.id_ranking = v.id_ranking")
        sb.Append(" LEFT JOIN ext_usuario u ON u.usuario = v.usuario")
    sb.Append(" LEFT JOIN ext_franquia f ON f.id_franquia = u.id_franquia")
    sb.Append(" WHERE r.id_ranking = '" & strIdRanking & "' AND v.cpf = '" & strCPF & "'")

        objDS = objBD.ExecutaQuery(sb.ToString())

        If objDS.Tables(0).Rows.Count <> 0 Then
            Dim objBERankeado As New BE.BERankeado
            objBERankeado = New BE.BERankeado()
            objBERankeado.IdRanking = strIdRanking
            objBERankeado.CPF = objDS.Tables(0).Rows(0)("cpf").ToString()
            If objDS.Tables(0).Rows(0)("usuario").ToString() <> "" Then
                objBERankeado.Usuario = New BE.BEUsuario(objDS.Tables(0).Rows(0)("usuario").ToString(), "")
            Else
                objBERankeado.Usuario = New BE.BEUsuario(objDS.Tables(0).Rows(0)("nome").ToString())
            End If
      objBERankeado.Franquia = objDS.Tables(0).Rows(0)("Filial").ToString()

            lstBERankeado.Add(objBERankeado)
        End If

        Return lstBERankeado
    End Function

    Public Sub SalvarRankeado(ByVal objBERankeado As BE.BERankeado)
        Dim objBD As New BancoDeDados
        Dim lstRankeados As List(Of BE.BERankeado)
        Try
            lstRankeados = BuscarRankeado(objBERankeado.IdRanking, objBERankeado.CPF)
            If lstRankeados.Count = 0 Then
                objBD.ExecutaNonQuery("INSERT INTO ext_vendedor (cpf, id_ranking, usuario, nome, filial) VALUES ('" & objBERankeado.CPF & "', '" & objBERankeado.IdRanking & "', " & IIf(objBERankeado.Usuario.Usuario = "", "NULL", "'" & objBERankeado.Usuario.Usuario & "'") & ", '" & objBERankeado.Usuario.Nome & "', '" & objBERankeado.Franquia & "')")
            ElseIf Not objBERankeado.Usuario Is Nothing Then
                If lstRankeados(0).Usuario.Usuario <> objBERankeado.Usuario.Usuario Then
                    objBD.ExecutaNonQuery("UPDATE ext_vendedor SET usuario = " & IIf(objBERankeado.Usuario.Usuario = "", "NULL", "'" & objBERankeado.Usuario.Usuario & "'") & ", nome = '" & objBERankeado.Usuario.Nome & "', filial = '" & objBERankeado.Franquia & "' WHERE Cpf = '" & objBERankeado.CPF & "' AND Id_Ranking = '" & objBERankeado.IdRanking & "'")
                End If
            End If
        Catch ex As ApplicationException

            Throw New ApplicationException("extranet.DA.DARankeado.SalvarRankeado(): " & ex.Message)

        End Try
    End Sub

    Public Sub ExcluirRankeado(ByVal objBERankeado As BE.BERankeado)
        Try
            Dim objBD As New BancoDeDados
            objBD.ExecutaNonQuery("DELETE FROM ext_vendedor WHERE id_ranking = '" & objBERankeado.IdRanking & "' AND cpf = '" & objBERankeado.CPF & "'")
        Catch ex As ApplicationException
            Throw New ApplicationException("extranet.DA.DARankeado.ExcluirRankeado(): " & ex.Message)
        End Try
    End Sub

End Class
